// ============================================================================
// NEO4J COMPREHENSIVE QUERY DEMONSTRATION
// ============================================================================
// This file contains queries from basic to advanced levels covering all
// Neo4j capabilities. Execute these in Neo4j Browser (http://localhost:7474)
//
// Credentials: neo4j / password123
// ============================================================================

// ============================================================================
// PART 1: BASIC QUERIES - READ OPERATIONS
// ============================================================================

// 1.1 Get all nodes in the database
MATCH (n) RETURN n LIMIT 25;

// 1.2 Count all nodes by label
MATCH (n)
RETURN labels(n)[0] AS Label, count(n) AS Count
ORDER BY Count DESC;

// 1.3 Count all relationships by type
MATCH ()-[r]->()
RETURN type(r) AS RelationshipType, count(r) AS Count
ORDER BY Count DESC;

// 1.4 Get all patients
MATCH (p:Patient)
RETURN p.id, p.name, p.gender, p.bloodType, p.allergies
ORDER BY p.name;

// 1.5 Get all diseases with their categories
MATCH (d:Disease)
RETURN d.id AS ICD_Code, d.name AS Disease, d.category, d.severity
ORDER BY d.category;

// 1.6 Get all medications by drug class
MATCH (m:Medication)
RETURN m.name AS Medication, m.drugClass, m.genericName, m.dosageForms
ORDER BY m.drugClass;


// ============================================================================
// PART 2: FILTERING AND CONDITIONS
// ============================================================================

// 2.1 Filter patients by gender
MATCH (p:Patient)
WHERE p.gender = 'Male'
RETURN p.name, p.gender, p.bloodType;

// 2.2 Find patients with allergies
MATCH (p:Patient)
WHERE size(p.allergies) > 0
RETURN p.name, p.allergies AS Allergies;

// 2.3 Find diseases by severity
MATCH (d:Disease)
WHERE d.severity IN ['moderate', 'severe']
RETURN d.name, d.severity, d.category;

// 2.4 Find medications containing specific string
MATCH (m:Medication)
WHERE m.name CONTAINS 'stat' OR m.genericName CONTAINS 'stat'
RETURN m.name, m.genericName, m.drugClass;

// 2.5 Patients born after 1985
MATCH (p:Patient)
WHERE p.dateOfBirth > date('1985-01-01')
RETURN p.name, p.dateOfBirth
ORDER BY p.dateOfBirth DESC;


// ============================================================================
// PART 3: RELATIONSHIP QUERIES
// ============================================================================

// 3.1 Find all patient diagnoses
MATCH (p:Patient)-[r:DIAGNOSED_WITH]->(d:Disease)
RETURN p.name AS Patient, d.name AS Disease, r.status
ORDER BY p.name;

// 3.2 Find all medications a patient takes
MATCH (p:Patient)-[r:TAKES]->(m:Medication)
RETURN p.name AS Patient, m.name AS Medication, r.dosage, r.frequency
ORDER BY p.name;

// 3.3 Find what diseases each medication treats
MATCH (m:Medication)-[r:TREATS]->(d:Disease)
RETURN m.name AS Medication, d.name AS Treats, r.effectiveness
ORDER BY m.name;

// 3.4 Find drug interactions
MATCH (m1:Medication)-[r:INTERACTS_WITH]->(m2:Medication)
RETURN m1.name AS Drug1, m2.name AS Drug2, r.description, r.severity;

// 3.5 Find patients NOT taking any medication
MATCH (p:Patient)
WHERE NOT (p)-[:TAKES]->(:Medication)
RETURN p.name AS PatientWithNoMedication;


// ============================================================================
// PART 4: MULTI-HOP QUERIES (Path Traversal)
// ============================================================================

// 4.1 Two-hop: Patient -> Disease -> Medication that treats it
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)<-[:TREATS]-(m:Medication)
RETURN p.name AS Patient, d.name AS Disease, m.name AS RecommendedMedication
ORDER BY p.name;

// 4.2 Find patients with same disease (co-morbidity analysis)
MATCH (p1:Patient)-[:DIAGNOSED_WITH]->(d:Disease)<-[:DIAGNOSED_WITH]-(p2:Patient)
WHERE p1.id < p2.id  // Avoid duplicates
RETURN p1.name AS Patient1, p2.name AS Patient2, d.name AS SharedDisease;

// 4.3 Find patients taking same medication
MATCH (p1:Patient)-[:TAKES]->(m:Medication)<-[:TAKES]-(p2:Patient)
WHERE p1.id < p2.id
RETURN p1.name AS Patient1, p2.name AS Patient2, m.name AS SharedMedication;

// 4.4 Three-hop: Find potential drug interactions for a patient
MATCH (p:Patient)-[:TAKES]->(m1:Medication)-[:INTERACTS_WITH]-(m2:Medication)<-[:TAKES]-(p)
RETURN DISTINCT p.name AS Patient, m1.name AS Drug1, m2.name AS Drug2
ORDER BY p.name;

// 4.5 Variable length path: Find all connections within 3 hops
MATCH path = (p:Patient {name: 'John Doe'})-[*1..3]-(connected)
RETURN path LIMIT 20;


// ============================================================================
// PART 5: AGGREGATIONS
// ============================================================================

// 5.1 Count diseases per patient
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
RETURN p.name AS Patient, count(d) AS DiseaseCount
ORDER BY DiseaseCount DESC;

// 5.2 Count patients per disease
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
RETURN d.name AS Disease, count(p) AS PatientCount
ORDER BY PatientCount DESC;

// 5.3 Average number of medications per patient
MATCH (p:Patient)
OPTIONAL MATCH (p)-[:TAKES]->(m:Medication)
WITH p, count(m) AS medCount
RETURN avg(medCount) AS AvgMedicationsPerPatient;

// 5.4 Patients grouped by blood type
MATCH (p:Patient)
RETURN p.bloodType AS BloodType, collect(p.name) AS Patients, count(p) AS Count
ORDER BY Count DESC;

// 5.5 Disease distribution by category
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
RETURN d.category AS Category, count(DISTINCT p) AS PatientCount, collect(DISTINCT d.name) AS Diseases
ORDER BY PatientCount DESC;


// ============================================================================
// PART 6: PATTERN COMPREHENSION & SUBQUERIES
// ============================================================================

// 6.1 List patients with their diseases as array
MATCH (p:Patient)
RETURN p.name AS Patient,
       [(p)-[:DIAGNOSED_WITH]->(d:Disease) | d.name] AS Diseases;

// 6.2 List patients with their medications as array
MATCH (p:Patient)
RETURN p.name AS Patient,
       [(p)-[:TAKES]->(m:Medication) | m.name] AS Medications;

// 6.3 Subquery: Find patients with more than 2 conditions
MATCH (p:Patient)
WHERE count { (p)-[:DIAGNOSED_WITH]->() } >= 2
RETURN p.name AS Patient, count { (p)-[:DIAGNOSED_WITH]->() } AS ConditionCount;

// 6.4 CALL subquery for complex operations
MATCH (p:Patient)
CALL {
    WITH p
    MATCH (p)-[:TAKES]->(m:Medication)
    RETURN count(m) AS medCount
}
WHERE medCount > 1
RETURN p.name AS Patient, medCount AS MedicationCount
ORDER BY medCount DESC;


// ============================================================================
// PART 7: CASE EXPRESSIONS & CONDITIONAL LOGIC
// ============================================================================

// 7.1 Categorize patients by age
MATCH (p:Patient)
WITH p, date().year - p.dateOfBirth.year AS age
RETURN p.name,
       age AS Age,
       CASE
           WHEN age < 30 THEN 'Young Adult'
           WHEN age < 50 THEN 'Adult'
           WHEN age < 65 THEN 'Middle Age'
           ELSE 'Senior'
       END AS AgeCategory
ORDER BY age;

// 7.2 Disease risk level based on count
MATCH (p:Patient)
WITH p, count { (p)-[:DIAGNOSED_WITH]->() } AS diseaseCount
RETURN p.name,
       diseaseCount,
       CASE
           WHEN diseaseCount >= 3 THEN 'High Risk'
           WHEN diseaseCount = 2 THEN 'Medium Risk'
           ELSE 'Low Risk'
       END AS RiskLevel
ORDER BY diseaseCount DESC;

// 7.3 Coalesce for null handling
MATCH (p:Patient)
RETURN p.name,
       coalesce(p.allergies[0], 'No known allergies') AS PrimaryAllergy;


// ============================================================================
// PART 8: SHORTEST PATH & PATH FINDING
// ============================================================================

// 8.1 Shortest path between two patients through any relationship
MATCH path = shortestPath(
    (p1:Patient {name: 'John Doe'})-[*]-(p2:Patient {name: 'Bob Wilson'})
)
RETURN path, length(path) AS PathLength;

// 8.2 All shortest paths between patients
MATCH path = allShortestPaths(
    (p1:Patient {name: 'John Doe'})-[*]-(p2:Patient {name: 'Charlie Brown'})
)
RETURN path, length(path) AS PathLength;

// 8.3 Find paths through specific relationship types
MATCH path = (p1:Patient)-[:DIAGNOSED_WITH|TAKES*1..4]-(p2:Patient)
WHERE p1.name = 'John Doe' AND p1 <> p2
RETURN DISTINCT p2.name AS ConnectedPatient, length(path) AS Hops
ORDER BY Hops;


// ============================================================================
// PART 9: GRAPH DATA SCIENCE (GDS) ALGORITHMS
// ============================================================================

// --- First, create a graph projection for GDS ---

// 9.0 Drop existing projection if exists
CALL gds.graph.drop('healthcare-graph', false) YIELD graphName;

// 9.1 Create in-memory graph projection
CALL gds.graph.project(
    'healthcare-graph',
    ['Patient', 'Disease', 'Medication'],
    {
        DIAGNOSED_WITH: {orientation: 'UNDIRECTED'},
        TAKES: {orientation: 'UNDIRECTED'},
        TREATS: {orientation: 'UNDIRECTED'}
    }
);

// --- CENTRALITY ALGORITHMS ---

// 9.2 PageRank - Find most influential nodes
CALL gds.pageRank.stream('healthcare-graph')
YIELD nodeId, score
WITH gds.util.asNode(nodeId) AS node, score
RETURN labels(node)[0] AS Label,
       CASE WHEN node.name IS NOT NULL THEN node.name ELSE node.id END AS Name,
       round(score * 1000) / 1000 AS PageRank
ORDER BY score DESC
LIMIT 10;

// 9.3 Degree Centrality - Most connected nodes
CALL gds.degree.stream('healthcare-graph')
YIELD nodeId, score
WITH gds.util.asNode(nodeId) AS node, score
RETURN labels(node)[0] AS Label,
       CASE WHEN node.name IS NOT NULL THEN node.name ELSE node.id END AS Name,
       toInteger(score) AS Connections
ORDER BY score DESC
LIMIT 10;

// 9.4 Betweenness Centrality - Bridge nodes
CALL gds.betweenness.stream('healthcare-graph')
YIELD nodeId, score
WITH gds.util.asNode(nodeId) AS node, score
WHERE score > 0
RETURN labels(node)[0] AS Label,
       CASE WHEN node.name IS NOT NULL THEN node.name ELSE node.id END AS Name,
       round(score * 100) / 100 AS BetweennessCentrality
ORDER BY score DESC
LIMIT 10;

// --- COMMUNITY DETECTION ---

// 9.5 Louvain Community Detection
CALL gds.louvain.stream('healthcare-graph')
YIELD nodeId, communityId
WITH gds.util.asNode(nodeId) AS node, communityId
RETURN communityId AS Community,
       collect(CASE WHEN node.name IS NOT NULL THEN node.name ELSE node.id END) AS Members,
       count(*) AS Size
ORDER BY Size DESC;

// 9.6 Label Propagation
CALL gds.labelPropagation.stream('healthcare-graph')
YIELD nodeId, communityId
WITH gds.util.asNode(nodeId) AS node, communityId
RETURN communityId AS Community,
       collect(CASE WHEN node.name IS NOT NULL THEN node.name ELSE node.id END) AS Members,
       count(*) AS Size
ORDER BY Size DESC;

// --- SIMILARITY ALGORITHMS ---

// 9.7 Node Similarity - Find similar patients
CALL gds.nodeSimilarity.stream('healthcare-graph')
YIELD node1, node2, similarity
WITH gds.util.asNode(node1) AS n1, gds.util.asNode(node2) AS n2, similarity
WHERE labels(n1)[0] = 'Patient' AND labels(n2)[0] = 'Patient'
RETURN n1.name AS Patient1, n2.name AS Patient2,
       round(similarity * 100) / 100 AS Similarity
ORDER BY similarity DESC
LIMIT 10;

// --- PATHFINDING ALGORITHMS ---

// 9.8 Dijkstra Shortest Path (if weighted relationships exist)
// Note: Works better with weighted edges - this is a demonstration
CALL gds.shortestPath.dijkstra.stream('healthcare-graph', {
    sourceNode: gds.util.nodeId('Patient', {id: 'P001'}),
    targetNode: gds.util.nodeId('Patient', {id: 'P003'})
})
YIELD index, sourceNode, targetNode, totalCost, nodeIds, costs, path
RETURN
    gds.util.asNode(sourceNode).name AS Source,
    gds.util.asNode(targetNode).name AS Target,
    totalCost AS Hops,
    [nodeId IN nodeIds | gds.util.asNode(nodeId).name] AS Path;

// 9.9 Memory estimation for algorithms
CALL gds.pageRank.mutate.estimate('healthcare-graph', {mutateProperty: 'pagerank'})
YIELD requiredMemory, nodeCount, relationshipCount;


// ============================================================================
// PART 10: APOC PROCEDURES (Awesome Procedures on Cypher)
// ============================================================================

// 10.1 Meta information about the graph
CALL apoc.meta.stats() YIELD nodeCount, relCount, labels, relTypes
RETURN nodeCount, relCount, labels, relTypes;

// 10.2 Graph schema visualization
CALL db.schema.visualization();

// 10.3 Convert node to map
MATCH (p:Patient {name: 'John Doe'})
RETURN apoc.map.fromNode(p) AS PatientMap;

// 10.4 Create virtual relationships for visualization
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)<-[:TREATS]-(m:Medication)
WHERE NOT (p)-[:TAKES]->(m)
RETURN p, d, m, apoc.create.vRelationship(p, 'SHOULD_TAKE', {reason: 'treats condition'}, m) AS recommendation;

// 10.5 Periodic iterate for batch processing (demo - be careful with large datasets)
// CALL apoc.periodic.iterate(
//     'MATCH (p:Patient) RETURN p',
//     'SET p.processed = true',
//     {batchSize: 100, parallel: false}
// );

// 10.6 Export data to JSON format
CALL apoc.export.json.query(
    'MATCH (p:Patient)-[r:DIAGNOSED_WITH]->(d:Disease) RETURN p, r, d LIMIT 5',
    null,
    {stream: true}
) YIELD data
RETURN data;

// 10.7 Text functions
MATCH (p:Patient)
RETURN p.name,
       apoc.text.upperCase(p.name) AS UpperCase,
       apoc.text.snakeCase(p.name) AS SnakeCase;


// ============================================================================
// PART 11: WRITE OPERATIONS (CREATE, UPDATE, DELETE)
// ============================================================================

// 11.1 Create a new patient
CREATE (p:Patient {
    id: 'P999',
    name: 'Demo Patient',
    gender: 'Other',
    bloodType: 'AB+',
    allergies: [],
    active: true,
    createdAt: datetime()
})
RETURN p;

// 11.2 Update patient properties
MATCH (p:Patient {id: 'P999'})
SET p.phone = '555-0199',
    p.updatedAt = datetime()
RETURN p;

// 11.3 Add a relationship
MATCH (p:Patient {id: 'P999'})
MATCH (d:Disease {id: 'I10'})
CREATE (p)-[r:DIAGNOSED_WITH {
    diagnosedDate: date(),
    diagnosedBy: 'Dr. Demo',
    status: 'active'
}]->(d)
RETURN p, r, d;

// 11.4 Delete relationship
MATCH (p:Patient {id: 'P999'})-[r:DIAGNOSED_WITH]->(d:Disease)
DELETE r
RETURN p.name AS Patient, 'relationship deleted' AS Status;

// 11.5 Delete node (clean up demo patient)
MATCH (p:Patient {id: 'P999'})
DETACH DELETE p;

// 11.6 Merge - Create if not exists, update if exists
MERGE (d:Disease {id: 'TEST01'})
ON CREATE SET d.name = 'Test Disease', d.createdAt = datetime()
ON MATCH SET d.updatedAt = datetime()
RETURN d;

// Clean up test disease
MATCH (d:Disease {id: 'TEST01'}) DELETE d;


// ============================================================================
// PART 12: INDEXES AND CONSTRAINTS
// ============================================================================

// 12.1 Show all constraints
SHOW CONSTRAINTS;

// 12.2 Show all indexes
SHOW INDEXES;

// 12.3 Create a full-text index for searching
CREATE FULLTEXT INDEX patientSearchIndex IF NOT EXISTS
FOR (p:Patient)
ON EACH [p.name, p.bloodType];

// 12.4 Use full-text search
CALL db.index.fulltext.queryNodes('patientSearchIndex', 'John OR Jane')
YIELD node, score
RETURN node.name, score
ORDER BY score DESC;

// 12.5 Create a composite index
CREATE INDEX patient_gender_bloodtype IF NOT EXISTS
FOR (p:Patient)
ON (p.gender, p.bloodType);


// ============================================================================
// PART 13: TRANSACTIONS AND EXPLAIN
// ============================================================================

// 13.1 Explain query execution plan
EXPLAIN MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
WHERE p.gender = 'Male'
RETURN p.name, d.name;

// 13.2 Profile query with actual metrics
PROFILE MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
WHERE p.gender = 'Male'
RETURN p.name, d.name;


// ============================================================================
// PART 14: DATE AND TIME OPERATIONS
// ============================================================================

// 14.1 Current date and time functions
RETURN date() AS Today,
       datetime() AS Now,
       time() AS CurrentTime,
       date().year AS Year,
       date().month AS Month,
       date().day AS Day;

// 14.2 Date arithmetic
MATCH (p:Patient)
RETURN p.name,
       p.dateOfBirth AS BirthDate,
       date().year - p.dateOfBirth.year AS Age,
       duration.between(p.dateOfBirth, date()).years AS ExactAge;

// 14.3 Filter by date range
MATCH (p:Patient)
WHERE p.dateOfBirth > date('1980-01-01') AND p.dateOfBirth < date('1995-01-01')
RETURN p.name, p.dateOfBirth
ORDER BY p.dateOfBirth;


// ============================================================================
// PART 15: ADVANCED PATTERNS
// ============================================================================

// 15.1 Find triangles (patients connected through same disease and medication)
MATCH (p1:Patient)-[:DIAGNOSED_WITH]->(d:Disease)<-[:DIAGNOSED_WITH]-(p2:Patient),
      (p1)-[:TAKES]->(m:Medication)<-[:TAKES]-(p2)
WHERE p1.id < p2.id
RETURN p1.name AS Patient1, p2.name AS Patient2,
       d.name AS SharedDisease, m.name AS SharedMedication;

// 15.2 Find recommendation: Medications other patients with same disease take
MATCH (p:Patient {name: 'John Doe'})-[:DIAGNOSED_WITH]->(d:Disease)<-[:DIAGNOSED_WITH]-(other:Patient)
MATCH (other)-[:TAKES]->(m:Medication)
WHERE NOT (p)-[:TAKES]->(m)
RETURN DISTINCT m.name AS SuggestedMedication,
       d.name AS ForCondition,
       count(DISTINCT other) AS PatientsUsingIt
ORDER BY PatientsUsingIt DESC;

// 15.3 Complex pattern: Treatment pathway analysis
MATCH (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)
OPTIONAL MATCH (p)-[:TAKES]->(m:Medication)-[:TREATS]->(d)
WITH p, d, collect(m.name) AS CurrentTreatments
OPTIONAL MATCH (m2:Medication)-[:TREATS]->(d)
WHERE NOT m2.name IN CurrentTreatments
RETURN p.name AS Patient,
       d.name AS Condition,
       CurrentTreatments,
       collect(DISTINCT m2.name) AS AlternativeTreatments;


// ============================================================================
// PART 16: UNION AND COMBINING RESULTS
// ============================================================================

// 16.1 Union of different entity types
MATCH (p:Patient)
RETURN 'Patient' AS Type, p.name AS Name, p.id AS ID
UNION ALL
MATCH (d:Disease)
RETURN 'Disease' AS Type, d.name AS Name, d.id AS ID
UNION ALL
MATCH (m:Medication)
RETURN 'Medication' AS Type, m.name AS Name, m.id AS ID;

// 16.2 UNWIND - Expand a list into rows
WITH ['Diabetes', 'Hypertension', 'Asthma'] AS conditions
UNWIND conditions AS condition
MATCH (d:Disease)
WHERE d.name CONTAINS condition
RETURN condition AS SearchTerm, d.name AS FoundDisease;


// ============================================================================
// PART 17: TRIGGERS (Using APOC)
// ============================================================================

// Note: Neo4j doesn't have built-in triggers, but APOC provides trigger functionality
// These must be enabled in configuration with apoc.trigger.enabled=true

// 17.1 View existing triggers
CALL apoc.trigger.list();

// 17.2 Create a trigger to log when patients are created (example - commented out)
// CALL apoc.trigger.add(
//     'patientCreated',
//     'UNWIND $createdNodes AS n
//      WITH n WHERE "Patient" IN labels(n)
//      CREATE (:AuditLog {action: "CREATED", nodeId: id(n), timestamp: datetime()})',
//     {phase: 'after'}
// );


// ============================================================================
// PART 18: CLEANUP - DROP GDS PROJECTION
// ============================================================================

// Drop the graph projection when done
CALL gds.graph.drop('healthcare-graph', false);


// ============================================================================
// QUICK REFERENCE QUERIES
// ============================================================================

// Database overview
CALL db.labels() YIELD label RETURN label;
CALL db.relationshipTypes() YIELD relationshipType RETURN relationshipType;

// Schema information
CALL db.schema.nodeTypeProperties();
CALL db.schema.relTypeProperties();

// Count everything
MATCH (n) RETURN count(n) AS TotalNodes;
MATCH ()-[r]->() RETURN count(r) AS TotalRelationships;
